﻿using System;
using System.Data.SQLite;
using System.IO;

namespace FileTransSystem
{
    static class GetSqlConnection
    {
        public static SQLiteConnection SqLiteConnection;

        public static void CreateDatabase()
        {
            var fileName = Directory.GetCurrentDirectory() + "\\user.db";
            SqLiteConnection = new SQLiteConnection("data source=" + fileName);
            SqLiteConnection.Open();
            SqLiteConnection.Close();
        }

        public static void CreateTable(SQLiteConnection sqLiteConnection)
        {
            SQLiteCommand CmdCreateUserTable = new SQLiteCommand();
            CmdCreateUserTable.Connection = sqLiteConnection;
            CmdCreateUserTable.CommandText =
                "create table userAccount" +
                "(id integer primary key autoincrement," +
                "account  text unique," +
                "password text," +
                "salt text," +
                "profile  text," +
                "sex varchar(10)," +
                "isAdmin  boolean)";
            CmdCreateUserTable.ExecuteNonQuery();
            SQLiteCommand CmdCreateFileTable = new SQLiteCommand();
            CmdCreateFileTable.Connection = sqLiteConnection;
            CmdCreateFileTable.CommandText =
                "create table fileInfo (id integer primary key autoincrement,fileName varchar(255),fileLength varchar(255),fileMD5 varchar(255),uploadTime varchar(255),uploadUser varchar(255),ownedBy varchar(255),filePath varchar(255),CONSTRAINT uc_fileInfo UNIQUE (fileMD5,ownedBy))";
            CmdCreateFileTable.ExecuteNonQuery();
        }

        public static SQLiteConnection Connect()
        {
            try
            {
                var fileName = Directory.GetCurrentDirectory() + "\\user.db";
                SqLiteConnection = new SQLiteConnection("data source=" + fileName);
                SqLiteConnection.Open();
                SQLiteCommand sqLiteCommand = SqLiteConnection.CreateCommand();
                sqLiteCommand.CommandText =
                    "select count(*) from sqlite_master where type='table' and name='userAccount'";
                if (0 == Convert.ToInt32(sqLiteCommand.ExecuteScalar()))
                {
                    CreateTable(SqLiteConnection);
                }

                return SqLiteConnection;
            }
            catch (SQLiteException e)
            {
                Console.WriteLine(e);
                throw;
            }
        }

        public static void CloseConnect()
        {
            try
            {
                SqLiteConnection.Close();
            }
            catch (SQLiteException e)
            {
                Console.WriteLine(e);
                throw;
            }
        }
    }
}